package com.authine.cloudpivot.ext.service.ipml;


import com.authine.cloudpivot.engine.api.facade.BizObjectFacade;
import com.authine.cloudpivot.foundation.orm.api.dml.JPANativeQuery;
import com.authine.cloudpivot.util.DateUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.hibernate.hql.internal.ast.SqlASTFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Slf4j
@Service
public class SyncBaseDataUpdateLogServiceImpl {
    @Autowired
    JPANativeQuery jpaNativeQuery;

    @PersistenceContext
    private EntityManager entityManager;



    @Transactional
    public void syncBaseDataUpdateLog() {
        String sql = "INSERT INTO base_update_log ( jdId, jdname, sqId, sqname,wgId,wgname, total, type, updateTime ) ";
        String queryDate = DateUtil.dateToStr(DateUtil.addDay(new Date(), -1), "yyyy-MM-dd");
        String jmSql = String.format("SELECT\n" +
                "r.belongJd jdId,\n" +
                "t1.NAME jdname,\n" +
                "belongSq sqId,\n" +
                "t2.NAME sqname,\n" +
                "r.affiliationDept wgId,\n" +
                "t3.name wgname,\n" +
                "count(*) total,\n" +
                "'jm' AS type,\n" +
                "DATE_FORMAT( r.modifiedTime, '%%Y-%%m-%%d' ) AS updateTime \n" +
                "FROM\n" +
                "\tiknn4_resident r\n" +
                "\tLEFT JOIN h_org_department t1 ON t1.id = r.belongJd\n" +
                "\tLEFT JOIN h_org_department t2 ON t2.id = r.belongSq \n" +
                "\tleft join h_org_department t3 on t3.id = r.affiliationDept\n" +
                "WHERE\n" +
                "\tr.belongJd IS NOT NULL and r. belongSq is not null \n" +
                "\tand DATE_FORMAT( r.modifiedTime, '%%Y-%%m-%%d' ) ='%s'\n" +
                "\tAND r.createdTime != r.modifiedTime \n" +
                "GROUP BY\n" +
                "\tr.belongJd,\n" +
                "\tr.belongSq,\n" +
                "\tr.affiliationDept,\n" +
                "\tupdateTime;", queryDate);
        String fwSql = String.format("SELECT\n" +
                "r.belongJd jdId,\n" +
                "t1.NAME jdname,\n" +
                "belongSq sqId,\n" +
                "t2.NAME sqname,\n" +
                "r.affiliationDept wgId,\n" +
                "t3.name wgname,\n" +
                "count(*) total,\n" +
                "'fw' AS type,\n" +
                "DATE_FORMAT( r.modifiedTime, '%%Y-%%m-%%d' ) AS updateTime \n" +
                "FROM\n" +
                "\tiknn4_house r\n" +
                "\tLEFT JOIN h_org_department t1 ON t1.id = r.belongJd\n" +
                "\tLEFT JOIN h_org_department t2 ON t2.id = r.belongSq \n" +
                "\tleft join h_org_department t3 on t3.id = r.affiliationDept\n" +
                "WHERE\n" +
                "\tr.belongJd IS NOT NULL and r. belongSq is not null \n" +
                "\tand DATE_FORMAT( r.modifiedTime, '%%Y-%%m-%%d' ) ='%s'\n" +
                "\tAND r.createdTime != r.modifiedTime \n" +
                "GROUP BY\n" +
                "\tr.belongJd,\n" +
                "\tr.belongSq,\n" +
                "\tr.affiliationDept,\n" +
                "\tupdateTime;",queryDate);
        String sqSql = String.format("SELECT\n" +
                "r.belongJd jdId,\n" +
                "t1.NAME jdname,\n" +
                "belongSq sqId,\n" +
                "t2.NAME sqname,\n" +
                "r.belongWg wgId,\n" +
                "t3.name wgname,\n" +
                "count(*) total,\n" +
                "'sq' AS type,\n" +
                "DATE_FORMAT( r.modifiedTime, '%%Y-%%m-%%d' ) AS updateTime \n" +
                "FROM\n" +
                "\tiknn4_business r\n" +
                "\tLEFT JOIN h_org_department t1 ON t1.id = r.belongJd\n" +
                "\tLEFT JOIN h_org_department t2 ON t2.id = r.belongSq \n" +
                "\tleft join h_org_department t3 on t3.id = r.belongWg\n" +
                "WHERE\n" +
                "\tr.belongJd IS NOT NULL and r. belongSq is not null \n" +
                "\tand DATE_FORMAT( r.modifiedTime, '%%Y-%%m-%%d' ) ='%s'\n" +
                "\tAND r.createdTime != r.modifiedTime \n" +
                "GROUP BY\n" +
                "\tr.belongJd,\n" +
                "\tr.belongSq,\n" +
                "\tr.belongWg,\n" +
                "\tupdateTime;",queryDate);
        String xxSql = String.format("SELECT\n" +
                "r.belongJd jdId,\n" +
                "t1.NAME jdname,\n" +
                "belongSq sqId,\n" +
                "t2.NAME sqname,\n" +
                "r.belongWg wgId,\n" +
                "t3.name wgname,\n" +
                "count(*) total,\n" +
                "'xx' AS type,\n" +
                "DATE_FORMAT( r.modifiedTime, '%%Y-%%m-%%d' ) AS updateTime \n" +
                "FROM\n" +
                "\tiknn4_school r\n" +
                "\tLEFT JOIN h_org_department t1 ON t1.id = r.belongJd\n" +
                "\tLEFT JOIN h_org_department t2 ON t2.id = r.belongSq \n" +
                "\tleft join h_org_department t3 on t3.id = r.belongWg\n" +
                "WHERE\n" +
                "\tr.belongJd IS NOT NULL and r. belongSq is not null \n" +
                "\tand DATE_FORMAT( r.modifiedTime, '%%Y-%%m-%%d' ) ='%s'\n" +
                "\tAND r.createdTime != r.modifiedTime \n" +
                "GROUP BY\n" +
                "\tr.belongJd,\n" +
                "\tr.belongSq,\n" +
                "\tr.belongWg,\n" +
                "\tupdateTime;",queryDate);
        String jmInsert = sql+jmSql;
        String fwInsert = sql+fwSql;
        String sqInsert = sql+sqSql;
        String xxInsert = sql+xxSql;
        executeUpdateQuery(jmInsert);
        executeUpdateQuery(fwInsert);
        executeUpdateQuery(sqInsert);
        executeUpdateQuery(xxInsert);
    }


    @Transactional
    public void syncChuishao(){
        String sql = "SELECT id,sequenceNo,xcx_shifoumanyi FROM `ikhmj_chuishao_001` where sequenceStatus = 'COMPLETED' and Radio1698973951943 = '是' and appletStatus = '待评价' and ifnull(xcx_shifoumanyi,'') <> '满意'";
        List<Map<String, Object>> list = jpaNativeQuery.getList(sql,null,null);
        for (Map<String, Object> map : list) {
            String id = map.get("id").toString();
            String updatesql = String.format("update ikhmj_chuishao_001 set xcx_shifoumanyi = '满意',appletStatus = '已完结' where id = '%s'",id);
            Query nativeQuery = entityManager.createNativeQuery(updatesql);
            nativeQuery.executeUpdate();
        }
    }

    private void executeUpdateQuery(String sqlQuery) {
        try {
            Query nativeQuery = entityManager.createNativeQuery(sqlQuery);
            nativeQuery.executeUpdate();
        } catch (Exception e) {
            log.error("数据库更新操作失败: " + e.getMessage());
        }
    }

    @Transactional
    public void userTotal(){
        String sql = "SELECT h1.id,h1.name\n" +
                "        FROM h_org_department h1\n" +
                "        WHERE h1.querycode LIKE  '1_1#3_1#5_1%'\n" +
                "         AND h1.NAME REGEXP '街道|社区|村|网格'\n" +
                "        AND h1.deleted = 0 and id != '2c9a8e6488e8fca60188e912ee421f97'";
        List<Map<String,Object>> list = jpaNativeQuery.getList(sql, null, null);
        for (Map<String, Object> map : list){
            String depId =(String) map.get("id");
            String name = (String) map.get("name");
            String querysql = String.format("SELECT COUNT(DISTINCT(userId)) total\n" +
                    "FROM h_org_dept_user\n" +
                    "WHERE deptId IN (\n" +
                    "    SELECT id\n" +
                    "    FROM (\n" +
                    "        SELECT h1.id,h1.name\n" +
                    "        FROM h_org_department h1\n" +
                    "        WHERE h1.querycode LIKE CONCAT(\n" +
                    "            (SELECT h2.queryCode FROM h_org_department h2 WHERE h2.id = '%s'),\n" +
                    "            '%%'\n" +
                    "        ) AND h1.NAME REGEXP '街道|社区|村|网格'\n" +
                    "        AND h1.deleted = 0\n" +
                    "    ) AS subquery \n" +
                    ");",depId);
            List<Map<String,Object>> list1 = jpaNativeQuery.getList(querysql, null, null);
            Object o = list1.get(0).get("total");
            String deptotal = String.format("select * from dep_user_total where deptId = '%s'",depId);
            List<Map<String,Object>> list2 = jpaNativeQuery.getList(deptotal, null, null);
            if(CollectionUtils.isNotEmpty(list2)){
                String updateSql = String.format("UPDATE dep_user_total SET total = '%s' WHERE deptId = '%s'", o, depId);
                Query nativeQuery = entityManager.createNativeQuery(updateSql);
                nativeQuery.executeUpdate();
            }else {
                String insertSql = String.format("INSERT INTO dep_user_total (`deptId`,`name`,`total`) VALUES ('%s', '%s','%s')", depId,name,o);
                Query nativeQuery = entityManager.createNativeQuery(insertSql);
                nativeQuery.executeUpdate();
            }
        }
    }
}
